﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 影楼婚纱摄影系统
{
    public partial class 营业额管理 : Form
    {
        public 营业额管理()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != "")
            {
                //数据库连接串
                string str = System.Configuration.ConfigurationManager.ConnectionStrings["marry"].ConnectionString;
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(str);
                    //打开数据库
                    conn.Open();
                    string sql = "Select convert(varchar,Year(ordertime)) as 年份,convert(varchar,Month(ordertime)) as 月份,SUM(price) as 当月营业额 FROM [order] where Year(ordertime) = '{0}' Group by Year(ordertime),Month(ordertime)  Union Select '合计' as 年份,'',SUM(price) as 当月营业额 FROM [order] where Year(ordertime) = '{0}'";
                    //填充占位符
                    sql = string.Format(sql, textBox1.Text);
                    //创建SqlDataAdapter类的对象
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    //创建DataSet类的对象
                    DataSet ds = new DataSet();
                    //使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
                    sda.Fill(ds);
                    //设置表格控件的DataSource属性
                    dataGridView1.DataSource = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    MessageBox.Show("出现错误！" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }

            else
            {
                MessageBox.Show("未输入查询时间！");
            }
        }


    }
}
